﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace 文件内容队列读写
{
    using System.Data;
    using System.Data.SQLite;
    using 文件内容队列读写.DataQueue;
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {

            InitializeComponent();
            Loaded += MainWindow_Loaded;
        }
        SQLiteDBHelper dbHelper = null;
        void MainWindow_Loaded(object sender, RoutedEventArgs e)
        {
            Loaded -= MainWindow_Loaded;
            dbHelper = new SQLiteDBHelper("./Datas/aa.db");
            InitTerminalDB();

            #region XMLnull值传输
            System.Xml.Serialization.XmlSerializer ser = new System.Xml.Serialization.XmlSerializer(typeof(aaa));
            string ss = "";
            using (System.IO.StringWriter sw = new System.IO.StringWriter())
            {
                ser.Serialize(sw, new aaa { aa = new double?[] { 1, 2, 3, null, 4, 6, 7, null } });
                ss = sw.ToString();
            }
            using (System.IO.StringReader sr = new System.IO.StringReader(ss))
            {
                var ddd = ser.Deserialize(sr);
            }
            #endregion




        }

        public class aaa { public double?[] aa { get; set; } }


        #region 数据库相关
        string createTable = @"CREATE TABLE TerminalData(
   ID INTEGER  PRIMARY KEY AUTOINCREMENT      NOT NULL,
   数据           TEXT    NOT NULL,
   终端号         NVARCHAR(100)     NOT NULL,
   写入时间        CHAR(15)
);";
        #endregion
        /// <summary>
        /// 初始化终端数据-数据库
        /// </summary>
        protected void InitTerminalDB()
        {
            try
            {            //1.创建数据库
                int n = dbHelper.ExecuteNonQuery(createTable, null);
            }
            catch { }
            try
            {            //1.设置数据库自动收缩
                int n = dbHelper.ExecuteNonQuery("PRAGMA auto_vacuum = 1", null);
            }
            catch { }
        }

        /// <summary>
        /// 向数据库写1万条数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            Test();

        }

        protected void Test(int startindex = 0)
        {
            string sql = string.Format("INSERT INTO TerminalData (数据,终端号,写入时间) VALUES ( '{0}','{1}','{2}')",
                "7E030800FF0841414141414141411A005E9798001505201409498703C200374134443039434146424132354133383330303732333644393532303034314214008FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E4002170081116683813000039901346000F5007F00C800D1DD0000010A0011000010110010000000AF7E7E030800FF0841414141414141411A005E9798001505201409498703C200374134443039434146424132354133383330303732333644393532303034314214008FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E408FC2F5285C8F0E4002170081116683813000039901346000F5007F00C800D1DD0000010A0011000010110010000000AF7E",
                "1234567890",
                DateTime.Now
                );
            string delsql = "delete from TerminalData where id={0}";


            Action act = () =>
            {
                //写入数据速度差不多100ms一条
                for (int i = 0; i < 100; i++)
                {
                    dbHelper.ExecuteNonQuery(sql, null);
                }
            };

            int beach = 100 * 100;
            Action act1 = () =>
            {
                List<string> sqls = new List<string>();
                for (int i = 0; i < beach; i++)
                {
                    sqls.Add(sql);
                }
                dbHelper.BatchExecuteInsert(sqls);

            };

            Action act2 = () =>
            {
                //删除100条数据 
                for (int i = startindex; i <= startindex + 100; i++)
                {
                    dbHelper.ExecuteNonQuery(string.Format(delsql, i), null);
                }
            };

            Action act3 = () =>
            {
                List<string> sqls = new List<string>();
                for (int i = startindex + 100; i <= startindex + beach + 100; i++)
                {
                    sqls.Add(string.Format(delsql, i));
                }
                dbHelper.BatchExecuteInsert(sqls);

            };

            long time = act.StopwatchRun();
            Console.WriteLine("普通添加100条用时:{0}毫秒", time);
            long time1 = act1.StopwatchRun();
            Console.WriteLine("批量添加{0}条用时:{1}毫秒", beach, time1);
            long time2 = act2.StopwatchRun();
            Console.WriteLine("普通删除100条用时:{0}毫秒", time2);
            long time3 = act3.StopwatchRun();
            Console.WriteLine("批量删除{0}条用时:{1}毫秒", beach, time3);
        }
        /// <summary>
        /// 循环写入,删除测试
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            for (int i = 0; i < 10; i++)
            {
                Console.WriteLine();
                Console.WriteLine("----开始第{0}次测试", i);
                Test(i * 10100);

            }

        }

        private void Button_Click_2(object sender, RoutedEventArgs e)
        {

            Action act = () =>
            {
                using (SQLiteConnection connection = new SQLiteConnection("Data Source=./Datas/aa.db"))
                {
                    connection.Open();
                    using (System.Data.Common.DbTransaction transaction = connection.BeginTransaction())
                    {
                        using (SQLiteCommand command = new SQLiteCommand(connection))
                        {

                        }
                        transaction.Commit();
                    }
                    connection.Close();
                }
            };

            long time = act.StopwatchRun();
            Console.WriteLine("打开关闭连接用时:{0}毫秒",  time);


            DataReader测试();
        }

        void DataReader测试()
        {
            SQLiteConnection conn = null;
            SQLiteDataReader reader = null;
            try
            {
                reader = dbHelper.ExecuteReader("select * from TerminalData", null, out conn);
                List<TerminalData> list = new List<DataQueue.TerminalData>();

                if (reader.HasRows)
                {

                    Action act = () => { list = reader.ReaderToList<TerminalData>(); };
                    long time = act.StopwatchRun();
                    Console.WriteLine("读取{0}条用时:{1}毫秒", list.Count, time);

                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

        }

        /// <summary>
        /// 多个不同数据库文件测试
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Button_Click_3(object sender, RoutedEventArgs e)
        {
            Window window = new Window() { Content = new 多个不同的数据库文件读写测试(), Title = "多个不同数据库文件测试" };
            window.Show();
            window.Closing += (ss, ev) => { window.Content = null; };
        }




    }
    /// <summary> 
    /// 说明：这是一个针对System.Data.SQLite的数据库常规操作封装的通用类。 
    /// 作者：zhoufoxcn(周公） 
    /// 日期：2010-04-01 
    /// Blog:http://zhoufoxcn.blog.51cto.com or http://blog.csdn.net/zhoufoxcn 
    /// Version:0.1 
    /// </summary> 
    public class SQLiteDBHelper
    {
        private string connectionString = string.Empty;
        /// <summary> 
        /// 构造函数 
        /// </summary> 
        /// <param name="dbPath">SQLite数据库文件路径</param> 
        public SQLiteDBHelper(string dbPath)
        {
            this.connectionString = "Data Source=" + dbPath;
        }
        /// <summary> 
        /// 创建SQLite数据库文件 
        /// </summary> 
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param> 
        public static void CreateDB(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
                    command.ExecuteNonQuery();
                    command.CommandText = "DROP TABLE Demo";
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 批量写入到库中
        /// </summary>
        /// <param name="sqls"></param>
        /// <returns></returns>
        public int BatchExecuteInsert(List<string> sqls)
        {

            int affectedRows = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (System.Data.Common.DbTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        foreach (var sql in sqls)
                        {
                            command.CommandText = sql;
                            affectedRows += command.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                }
            }
            return affectedRows;

        }

        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (System.Data.Common.DbTransaction transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        command.CommandText = sql;
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters);
                        }
                        affectedRows = command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            return affectedRows;
        }
        /// <summary> 
        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters, out SQLiteConnection connection)
        {
            connection = new SQLiteConnection(connectionString);
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        /// <summary> 
        /// 执行一个查询语句，返回查询结果的第一行第一列 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    return data;
                }
            }
        }
        /// <summary> 
        /// 查询数据库中的所有数据类型信息 
        /// </summary> 
        /// <returns></returns> 
        public DataTable GetSchema()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                DataTable data = connection.GetSchema("TABLES");
                connection.Close();
                //foreach (DataColumn column in data.Columns) 
                //{ 
                // Console.WriteLine(column.ColumnName); 
                //} 
                return data;
            }
        }
    }



}
